﻿//=================================
// 数据库对象操作类
// author:郑金泉
// created:2010-04-18
//=================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using MySql.Data;
using MySql.Data.MySqlClient;
using Obsidian.Utils;
using Obsidian.Edm;

namespace Obsidian.Data.Sql.MySqlClient
{
    /// <summary>
    /// 数据库对象操作类
    /// author:郑金泉
    /// created:2010-04-18
    /// updated:2014-01-04
    /// </summary>
    public class MySqlDbHandler : DbHandler, IDbHandler
    {

        private MySqlExecutor executor = new MySqlExecutor();
        
        public MySqlDbHandler() { }

        public MySqlDbHandler(IModel model)
        {
            this.OperateModel = model;
        }

        private void PrepareExecutor()
        {
            executor.ConnectionString = this.OperateModel.GetDataBase().ConnectionString;
        }

        #region Insert

        public object Insert()
        {
            MySqlParameter[] dbParams;
            string sqlText = this.BuildInsertCmdText(out dbParams);
            this.PrepareExecutor();
            object val = executor.ExecuteScalar(sqlText, dbParams);
            return val;
        }

        private string BuildInsertCmdText(out MySqlParameter[] sqlParams)
        {
            List<MySqlParameter> paramList = new List<MySqlParameter>();
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO `")
                .Append(this.OperateModel.DbTableName)
                .Append("` (");
            foreach (OperateField of in this.fieldList)
            {
                DbField dbf = of.ModelField.GetDbField();
                sb.Append("`").Append(dbf.Name).Append("`,");
            }
            sb.Remove(sb.Length - 1, 1).Append(") VALUES (");
            int n = 0;
            foreach (OperateField of in this.fieldList)
            {
                string paramName = "@P_" + n.ToString();
                sb.Append(paramName).Append(",");
                paramList.Add(new MySqlParameter(paramName, of.DbValue));
                n++;
            }
            sb.Remove(sb.Length - 1, 1).Append("); SELECT @@IDENTITY;");
            sqlParams = paramList.ToArray();
            return sb.ToString();
        }

        #endregion

        #region Update

        public int Update()
        {
            MySqlParameter[] dbParams;
            string sqlText = this.BuildUpdateCmdText(out dbParams);
            this.PrepareExecutor();
            int n = executor.ExecuteNonQuery(sqlText, dbParams);
            return n;
        }

        private string BuildUpdateCmdText(out MySqlParameter[] sqlParams)
        {
            List<MySqlParameter> paramList = new List<MySqlParameter>();
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE `")
                .Append(this.OperateModel.DbTableName)
                .Append("` SET ");
            int n = 0;
            foreach (OperateField of in this.fieldList)
            {
                string paramName = "@P_" + n.ToString();
                sb.Append("`").Append(of.ModelField.GetDbField().Name).Append("`=")
                    .Append(paramName).Append(",");
                paramList.Add(new MySqlParameter(paramName, of.DbValue));
                n++;
            }
            sb.Remove(sb.Length - 1, 1);

            if (this.whereCondition.SubConditions.Count > 0)
                sb.Append("\r\n").Append(" WHERE ").Append(this.whereCondition.BuildSqlText()).Append(";");
            else
                throw new Exception("警告！为确保安全，更新操作where条件不能为空！");

            sqlParams = paramList.ToArray();
            return sb.ToString();
        }


        #endregion

        #region Select

        public bool SelectFirstTo(params IModel[] models)
        {
            DataReader dr = this.Select();
            if (dr.Read())
            {
                dr.ReadTo(models);
                dr.Close();
                return true;
            }
            else
            {
                dr.Close();
                return false;
            }
        }

        public DataReader Select()
        {
            string sqlText = this.BuildSelectCmdText();
            this.PrepareExecutor();
            IDataReader dr = executor.ExecuteReader(sqlText);
            return new DataReader(dr);
        }

        public DataReader Select(QueryInfo qry)
        {
            ListAttrInfo listAttr = null;
            return this.Select(qry, out listAttr);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="qry"></param>
        /// <param name="listAttr"></param>
        /// <returns></returns>
        public DataReader Select(QueryInfo qry, out ListAttrInfo listAttr)
        {
            if (qry == null)
                throw new Exception("qry参数不能为空!");

            CheckQueryInfo(qry);

            listAttr = new ListAttrInfo();
            string sqlText = null;

            StringBuilder sb = new StringBuilder();
            sb.Append("\r\n").Append(this.BuildSelectItemsCountCmdText());
            sb.Append("\r\n").Append(this.BuildPaginationCmdText(qry));
            sqlText = sb.ToString();

            this.PrepareExecutor();
            IDataReader dr = executor.ExecuteReader(sqlText);
            if (dr.Read())
            {
                int itemsCount = Convert.ToInt32(dr[0]);
                int pagesCount = MathUtil.PagesCount(qry.PageSize.Value, itemsCount);
                listAttr.ItemsCount.Set(itemsCount);
                listAttr.PageNo.Set(qry.PageNo.Value);
                listAttr.PageSize.Set(qry.PageSize.Value);
                listAttr.PagesCount.Set(pagesCount);
                dr.NextResult();
            }
            else
            {
                throw new Exception("读取总数量出错！");
            }
            return new DataReader(dr);


        }

        /// <summary>
        /// 查询总数量
        /// </summary>
        /// <returns></returns>
        public int SelectItemsCount()
        {
            string sqlText = this.BuildSelectItemsCountCmdText();
            this.PrepareExecutor();
            return Convert.ToInt32(executor.ExecuteScalar(sqlText));
        }

        public int SelectItemsCount(QueryInfo qry)
        {
            ListAttrInfo listAttr = null;
            return this.SelectItemsCount(qry, out listAttr);
        }

        public int SelectItemsCount(QueryInfo qry, out ListAttrInfo listAttr)
        {
            if (qry == null)
                throw new Exception("qry参数不能为空!");

            CheckQueryInfo(qry);

            listAttr = new ListAttrInfo();
            string sqlText = null;

            StringBuilder sb = new StringBuilder();
            sb.Append("\r\n").Append(this.BuildSelectItemsCountCmdText());
            sqlText = sb.ToString();

            this.PrepareExecutor();
            IDataReader dr = executor.ExecuteReader(sqlText);
            int itemsCount = 0;
            if (dr.Read())
            {
                itemsCount = Convert.ToInt32(dr[0]);
                int pagesCount = MathUtil.PagesCount(qry.PageSize.Value, itemsCount);
                listAttr.ItemsCount.Set(itemsCount);
                listAttr.PageNo.Set(qry.PageNo.Value);
                listAttr.PageSize.Set(qry.PageSize.Value);
                listAttr.PagesCount.Set(pagesCount);
            }
            else
            {
                throw new Exception("读取总数量出错！");
            }
            return itemsCount;
        }

        private string BuildSelectItemsCountCmdText()
        {
            string joinText = this.BuildJoinText();
            string whereText = null;
            if (this.whereCondition.SubConditions.Count > 0)
                whereText = this.whereCondition.BuildSqlText();

            StringBuilder sb = new StringBuilder();
            sb.Append("\r\n").Append(" SELECT COUNT(*) FROM `")
                .Append(this.OperateModel.GetDbTable().Name)
                .Append("`");
            if (!String.IsNullOrEmpty(joinText))
                sb.Append("\r\n").Append(joinText);
            if (!String.IsNullOrEmpty(whereText))
                sb.Append("\r\n").Append(" WHERE ").Append(whereText);
            sb.Append(";");
            string sqlText = sb.ToString();
            return sqlText;
        }

        /// <summary>
        /// 构建分页查询SQL语句
        /// </summary>
        /// <param name="qry"></param>
        /// <returns></returns>
        private string BuildPaginationCmdText(QueryInfo qry)
        {

            int start = qry.PageSize.Value * (qry.PageNo.Value - 1);
            if (start < 0) start = 0;
            StringBuilder sb = new StringBuilder();
            sb.Append(this.BuildSelectCmdText());
            sb.Remove(sb.Length - 1, 1);
            sb.Append(String.Format(" LIMIT {0},{1} ;", start, qry.PageSize.Value));
            return sb.ToString();
        }

        /// <summary>
        /// 构造查询语句
        /// </summary>
        /// <returns></returns>
        private string BuildSelectCmdText()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ");

            int n = 0;
            foreach (OperateField field in this.fieldList)
            {
                DbField dbField = field.ModelField.GetDbField();
                if (dbField != null)
                {
                    sb.Append("\r\n").Append(dbField.TablePrefixName).Append(" AS ").Append(dbField.QueryAlias)
                       .Append((n == this.fieldList.Count - 1) ? "" : ",");
                    n++;
                }
            }
            sb.Append("\r\n").Append(" FROM `")
                .Append(this.OperateModel.GetDbTable().Name)
                .Append("` ");

            string joinText = this.BuildJoinText();
            if (!String.IsNullOrEmpty(joinText))
                sb.Append("\r\n").Append(joinText);

            if (this.whereCondition.SubConditions.Count > 0)
                sb.Append("\r\n").Append(" WHERE ").Append(this.whereCondition.BuildSqlText());

            string orderByStr = this.BuildOrderByText();
            if (!String.IsNullOrEmpty(orderByStr))
                sb.Append("\r\n").Append(" ORDER BY ").Append(orderByStr);

            return sb.Append(";").ToString();
        }

        #endregion

        #region Delete

        public int Delete()
        {
            string sqlText = this.BuildDeleteCmdText();
            this.PrepareExecutor();
            int n = executor.ExecuteNonQuery(sqlText);
            return n;
        }

        private string BuildDeleteCmdText()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("DELETE FROM `")
                .Append(this.OperateModel.GetDbTable().Name)
                .Append("`");

            string joinText = this.BuildJoinText();
            if (!String.IsNullOrEmpty(joinText))
            {
                sb.Append(" FROM `")
                    .Append(this.OperateModel.GetDbTable().Name)
                    .Append("`");
                sb.Append("\r\n").Append(joinText);
            }

            if (this.whereCondition.SubConditions.Count > 0)
                sb.Append("\r\n").Append(" WHERE ").Append(this.whereCondition.BuildSqlText());
            else
                throw new Exception("警告！为确保安全，更新操作where条件不能为空！");
            return sb.Append(";").ToString();
        }

        #endregion

        #region

        private string BuildJoinText()
        {
            if (this.dictJoinClauses.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                foreach (string key in this.dictJoinClauses.Keys)
                {
                    JoinClause joinClause = dictJoinClauses[key];
                    sb.Append(joinClause.BuildSqlText());
                }
                return sb.ToString();
            }
            else
            {
                return null;
            }
        }

        public IDbHandler OrderBy(IModelField field)
        {
            OrderByClause clause = new OrderByClause(field, OrderType.ASC);
            this.orderByList.Add(clause);
            return this;
        }

        public IDbHandler OrderBy(IModelField field, OrderType type)
        {
            OrderByClause clause = new OrderByClause(field, type);
            this.orderByList.Add(clause);
            return this;
        }

        private string BuildOrderByText()
        {
            if (this.orderByList.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                foreach (OrderByClause clause in this.orderByList)
                    sb.Append(clause.BuildSqlText()).Append(",");
                sb.Remove(sb.Length - 1, 1);
                return sb.ToString();
            }
            else
            {
                return null;
            }
        }

        #endregion
    }

}
